﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Serialization;
using BlogEngine.Core;
using BlogEngine.Core.DataStore;
using BlogEngine.Core.Providers;

namespace BlogEngine.SQLServer
{
    public class SqlBlogProvider : BlogProvider
    {
        internal string connString;
        internal string tablePrefix;
        internal string schema;
        private XmlMappingSource blogDataMappingSource;
        private bool isMultiBlog = false;

        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (String.IsNullOrEmpty(name))
            {
                name = "SqlBlogProvider";
            }

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "SQL Server Blog Provider");
            }

            base.Initialize(name, config);

            if (config["connectionStringName"] == null)
            {
                // default to BlogEngine
                config["connectionStringName"] = "BlogEngine";
            }
            connString = ConfigurationManager.ConnectionStrings[config["connectionStringName"]].ConnectionString;
            config.Remove("connectionStringName");

            if (config["tablePrefix"] == null)
            {
                // default
                config["tablePrefix"] = "be_";
            }
            tablePrefix = config["tablePrefix"];
            config.Remove("tablePrefix");

            if (config["multiblog"] != null)
            {
                isMultiBlog = config["multiblog"].ToLower() == "true";
                config.Remove("multiblog");
            }

            if (config["schema"] == null)
            {
                config["schema"] = "dbo";
            }
            schema = config["schema"].Replace(".", "");
            config.Remove("schema");

            // Throw an exception if unrecognized attributes remain
            if (config.Count > 0)
            {
                string attr = config.GetKey(0);
                if (!String.IsNullOrEmpty(attr))
                    throw new ProviderException("Unrecognized attribute: " + attr);
            }
        }

        public override Post SelectPost(Guid id)
        {
            Post post = null;
            using (BlogData.BlogDataContext context = getNewContext())
            {
                // We want a dump of all the post data in one go:
                DataLoadOptions options = new DataLoadOptions();
                options.LoadWith<BlogData.Post>(p => p.PostTags);
                options.LoadWith<BlogData.Post>(p => p.PostCategories);
                options.LoadWith<BlogData.Post>(p => p.PostComments);
                options.LoadWith<BlogData.Post>(p => p.PostNotifies);
                context.LoadOptions = options;

                BlogData.Post postData = (from p in context.Posts
                                      where p.PostID == id
                                      select p).FirstOrDefault();

                if (postData != null)
                {
                    post = createPostFromData(postData);
                }
            }
            return post;
        }

        private Post createPostFromData(BlogData.Post postData)
        {
            Post post = new Post()
            {
                Id = postData.PostID,
                Title = postData.Title,
                Content = postData.PostContent,
                Description = postData.Description == null ? string.Empty : postData.Description,
                DateCreated = postData.DateCreated ?? DateTime.MinValue,
                DateModified = postData.DateModified ?? DateTime.MinValue,
                Author = postData.Author,
                IsPublished = postData.IsPublished ?? false,
                IsCommentsEnabled = postData.IsCommentEnabled ?? false,
                Raters = postData.Raters ?? int.MinValue,
                Rating = postData.Rating ?? float.MinValue,
                Slug = postData.Slug
            };

            post.Tags.AddRange(postData.PostTags.Select(t => t.Tag));

            foreach (BlogData.PostCategory cat in postData.PostCategories)
            {
                Category category = Category.GetCategory(cat.CategoryID);
                if (category != null)
                {
                    post.Categories.Add(Category.GetCategory(cat.CategoryID));
                }
            }

            post.Comments.AddRange(postData.PostComments.Select(c => createCommentFromData(c, post)));
            post.Comments.Sort();

            post.NotificationEmails.AddRange(postData.PostNotifies.Select(p => p.NotifyAddress).ToArray());

            return post;
        }

        private static Comment createCommentFromData(BlogData.PostComment commentData, Post parent)
        {
            Comment comment = new Comment()
            {
                Id = commentData.PostCommentID,
                IsApproved = commentData.IsApproved ?? true,
                Author = commentData.Author,
                Email = commentData.Email,
                Content = commentData.Comment,
                DateCreated = commentData.CommentDate,
                Parent = parent,
                Country = commentData.Country,
                IP = commentData.Ip
            };
            if (!string.IsNullOrEmpty(commentData.Website))
            {
                Uri website;
                if (Uri.TryCreate(commentData.Website, UriKind.Absolute, out website))
                {
                    comment.Website = website;
                }
            }
            return comment;
        }

        public override void InsertPost(Post post)
        {
            BlogData.Post postData = createDataFromPost(post);

            using (BlogData.BlogDataContext context = getNewContext())
            {
                context.Posts.InsertOnSubmit(postData);

                // This is for import or for those of us too lazy to select tags manually.
                if (post.Tags.Count <= 0)
                {
                    post.Tags.AddRange(findTags(post));
                }

                updateTags(context, post);
                updateCategories(context, post);
                updateComments(context, post);
                updateNotifies(context, post);

                if (isMultiBlog)
                {
                    BlogData.BlogPost blogPost = new BlogEngine.SQLServer.BlogData.BlogPost()
                    {
                        BlogId = Utils.GetBlogId(),
                        PostId = post.Id
                    };
                    context.BlogPosts.InsertOnSubmit(blogPost);
                }
                context.SubmitChanges();
            }
        }

        private List<string> findTags(Post post)
        {
            Regex relRegex = new Regex(@"\s+rel\s*=\s*(""[^""]*?\btag\b.*?""|'[^']*?\btag\b.*?')", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            Regex hrefRegex = new Regex(@"\s+href\s*=\s*(""(?<url>[^""]*?)""|'(?<url>[^']*?)')", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            Regex anchorRegex = new Regex(@"<a(\s+\w+\s*=\s*(?:""[^""]*?""|'[^']*?')(?!\w))+\s*>.*?</a>", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            
            string html = post.Content;

            List<string> tags = new List<string>();
            List<string> loweredTags = new List<string>();

            foreach (Match m in anchorRegex.Matches(html))
            {
                string anchorHtml = m.Value;
                if (!relRegex.IsMatch(anchorHtml))
                    continue;

                Match urlMatch = hrefRegex.Match(anchorHtml);
                if (urlMatch.Success)
                {
                    string urlStr = urlMatch.Groups["url"].Value;
                    if (urlStr.EndsWith("/default.aspx", StringComparison.InvariantCultureIgnoreCase))
                        urlStr = urlStr.Substring(0, urlStr.Length - 13);
                    Uri url;
                    if (Uri.TryCreate(urlStr, UriKind.RelativeOrAbsolute, out url))
                    {
                        string[] seg = url.Segments;
                        string tag = System.Web.HttpUtility.UrlDecode(seg[seg.Length - 1].Replace("/", ""));

                        //Keep a list of lowered tags so we can prevent duplicates without modifying capitalization
                        string loweredTag = tag.ToLower();
                        if (!loweredTags.Contains(loweredTag))
                        {
                            loweredTags.Add(loweredTag);
                            tags.Add(tag);
                        }
                    }
                }
            }
            return tags;
        }

        private void updateNotifies(BlogData.BlogDataContext context, Post post)
        {
            IEnumerable<string> currentNotify = from note in context.PostNotifies
                                              where note.PostID == post.Id
                                              select note.NotifyAddress;
            IEnumerable<string> deleteNotify = from note in currentNotify
                                               where !post.NotificationEmails.Contains(note)
                                               select note;
            IEnumerable<BlogData.PostNotify> deleteNotifyData = from note in context.PostNotifies
                                                            where note.PostID == post.Id
                                                            && deleteNotify.Contains(note.NotifyAddress)
                                                            select note;
            IEnumerable<BlogData.PostNotify> newNotify = from string note in post.NotificationEmails
                                                     where !currentNotify.Contains(note)
                                                     select createDataFromNotify(post, note);

            context.PostNotifies.DeleteAllOnSubmit(deleteNotifyData);
            context.PostNotifies.InsertAllOnSubmit(newNotify);
        }

        private BlogData.PostNotify createDataFromNotify(Post post, string notify)
        {
            return new BlogData.PostNotify()
            {
                PostID = post.Id,
                NotifyAddress = notify
            };
        }

        private void updateComments(BlogData.BlogDataContext context, Post post)
        {
            IEnumerable<Guid> currentComments = from com in context.PostComments
                                                where com.PostID == post.Id
                                                select com.PostCommentID;
            IEnumerable<Guid> deleteComments = from com in currentComments
                                               where !post.Comments.Select(c => c.Id).Contains(com)
                                               select com;
            IEnumerable<BlogData.PostComment> deleteCommentData = from com in context.PostComments
                                                              where com.PostID == post.Id
                                                              && deleteComments.Contains(com.PostCommentID)
                                                              select com;
            IEnumerable<BlogData.PostComment> newComments = from com in post.Comments
                                                        where !currentComments.Contains(com.Id)
                                                        select createDataFromComment(post, com);

            context.PostComments.DeleteAllOnSubmit(deleteCommentData);
            context.PostComments.InsertAllOnSubmit(newComments);
        }

        private BlogData.PostComment createDataFromComment(Post post, Comment com)
        {
            return new BlogEngine.SQLServer.BlogData.PostComment()
            {
                PostCommentID = com.Id,
                PostID = post.Id,
                IsApproved = com.IsApproved,
                Author = com.Author,
                Email = com.Email,
                Comment = com.Content,
                CommentDate = com.DateCreated,
                Country = com.Country,
                Ip = com.IP,
                Website = com.Website != null ? com.Website.AbsoluteUri : ""
            };
        }

        private void updateCategories(BlogData.BlogDataContext context, Post post)
        {
            IEnumerable<Guid> currentCats = from cat in context.PostCategories
                                                         where cat.PostID == post.Id
                                                         select cat.CategoryID;
            IEnumerable<Guid> deleteCats = from cat in currentCats
                                           where !post.Categories.Select(c => c.Id).Contains(cat)
                                           select cat;
            IEnumerable<BlogData.PostCategory> deleteCatsData = from cat in context.PostCategories
                                                            where cat.PostID == post.Id
                                                            && deleteCats.Contains(cat.CategoryID)
                                                            select cat;
            IEnumerable<BlogData.PostCategory> newCats = from cat in post.Categories
                                                     where cat.Id == null || !currentCats.Contains(cat.Id)
                                                     select createDataFromCategory(post, cat);

            context.PostCategories.DeleteAllOnSubmit(deleteCatsData);
            context.PostCategories.InsertAllOnSubmit(newCats);
        }

        private BlogData.PostCategory createDataFromCategory(Post post, Category category)
        {
            return new BlogData.PostCategory()
            {
                PostID = post.Id,
                CategoryID = category.Id
            };
        }

        private void updateTags(BlogData.BlogDataContext context, Post post)
        {
            // this is inordinately complicated because
            //      a) the tag objects don't compare properly because new tags don't necessarily have a TagId
            //      b) you have to delete existing tags for a post (if any) while inserting new ones (if any)
            IEnumerable<string> currentTags = from tag in context.PostTags
                                                    where tag.PostID == post.Id
                                                    select tag.Tag;
            IEnumerable<string> deleteTags = from tag in currentTags
                                             where !post.Tags.Contains(tag)
                                             select tag;
            IEnumerable<BlogData.PostTag> deleteTagData = from tag in context.PostTags
                                                      where tag.PostID == post.Id
                                                      && deleteTags.Contains(tag.Tag)
                                                      select tag;
            IEnumerable<BlogData.PostTag> newTags = from tag in post.Tags
                                                where !currentTags.Contains(tag)
                                                select createDataFromTag(post, tag);

            context.PostTags.DeleteAllOnSubmit(deleteTagData);
            context.PostTags.InsertAllOnSubmit(newTags);
        }

        private BlogData.PostTag createDataFromTag(Post post, string tag)
        {
            return new BlogData.PostTag()
            {
                PostID = post.Id,
                Tag = tag
            };
        }

        

        private BlogData.Post createDataFromPost(Post post)
        {
            BlogData.Post postData = new BlogData.Post()
            {
                PostID = post.Id,
                Title = post.Title,
                PostContent = post.Content,
                Description = post.Description,
                DateCreated = post.DateCreated,
                DateModified = post.DateModified,
                Author = post.Author,
                IsPublished = post.IsPublished,
                IsCommentEnabled = post.IsCommentsEnabled,
                Raters = post.Raters,
                Rating = post.Rating,
                Slug = post.Slug
            };
            if (postData.DateModified == DateTime.MinValue)
            {
                postData.DateModified = postData.DateCreated;
            }

            return postData;
        }

        public override void UpdatePost(Post post)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                BlogData.Post postData = (from p in context.Posts
                                      where p.PostID == post.Id
                                      select p).FirstOrDefault();
                if (postData != null)
                {
                    updateDataFromPost(post, postData);

                    updateTags(context, post);
                    updateCategories(context, post);
                    updateComments(context, post);
                    updateNotifies(context, post);
                    context.SubmitChanges();
                }
            }
        }

        private void updateDataFromPost(Post post, BlogData.Post postData)
        {
            postData.PostID = post.Id;
            postData.Title = post.Title;
            postData.Description = post.Description;
            postData.PostContent = post.Content;
            postData.DateCreated = post.DateCreated;
            postData.DateModified = post.DateModified;
            postData.Author = post.Author;
            postData.IsPublished = post.IsPublished;
            postData.IsCommentEnabled = post.IsCommentsEnabled;
            postData.Raters = post.Raters;
            postData.Rating = post.Rating;
            postData.Slug = post.Slug;
        }

        public override void DeletePost(Post post)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var postData = from p in context.Posts
                               where p.PostID == post.Id
                               select p;
                var tags = from tag in context.PostTags
                           where tag.PostID == post.Id
                           select tag;
                var cats = from cat in context.PostCategories
                           where cat.PostID == post.Id
                           select cat;
                var coms = from com in context.PostComments
                           where com.PostID == post.Id
                           select com;
                var notes = from note in context.PostNotifies
                            where note.PostID == post.Id
                            select note;

                if (isMultiBlog)
                {
                    var blogPosts = postData.SelectMany(p => p.BlogPosts);
                    context.BlogPosts.DeleteAllOnSubmit(blogPosts);
                }

                context.Posts.DeleteAllOnSubmit(postData);
                context.PostTags.DeleteAllOnSubmit(tags);
                context.PostCategories.DeleteAllOnSubmit(cats);
                context.PostComments.DeleteAllOnSubmit(coms);
                context.PostNotifies.DeleteAllOnSubmit(notes);
                context.SubmitChanges();
            }
        }

        public override List<Post> FillPosts()
        {
            List<Post> posts = new List<Post>();
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var postIds = from post in context.Posts
                              select post.PostID;

                if (isMultiBlog)
                {
                    postIds = from post in context.Posts
                                join blogPosts in context.BlogPosts on post.PostID equals blogPosts.PostId
                                where blogPosts.BlogId == Utils.GetBlogId()
                                select post.PostID;
                }
                posts.AddRange(postIds.Select(p => Post.Load(p)));
            }

            posts.Sort();
            return posts;
        }

        public override Page SelectPage(Guid id)
        {
            Page page = null;
            using (BlogData.BlogDataContext context = getNewContext())
            {
                BlogData.Page pageData = (from p in context.Pages
                                      where p.PageID == id
                                      select p).FirstOrDefault();
                if (pageData != null)
                {
                    page = new Page()
                    {
                        Id = pageData.PageID,
                        Title = pageData.Title,
                        Description = pageData.Description,
                        Content = pageData.PageContent,
                        Keywords = pageData.Keywords,
                        DateCreated = pageData.DateCreated ?? DateTime.MinValue,
                        DateModified = pageData.DateModified ?? DateTime.MinValue,
                        IsPublished = pageData.IsPublished ?? false,
                        IsFrontPage = pageData.IsFrontPage ?? false,
                        Parent = pageData.Parent ?? Guid.Empty,
                        ShowInList = pageData.ShowInList ?? false
                    };
                }
            }
            return page;
        }

        public override void InsertPage(Page page)
        {
            BlogData.Page pageData = new BlogData.Page()
            {
                PageID = page.Id,
                Title = page.Title,
                Description = page.Description,
                PageContent = page.Content,
                Keywords = page.Keywords,
                DateCreated = page.DateCreated,
                DateModified = page.DateModified,
                IsPublished = page.IsPublished,
                IsFrontPage = page.IsFrontPage,
                Parent = page.Parent,
                ShowInList = page.ShowInList
            };
            using (BlogData.BlogDataContext context = getNewContext())
            {
                context.Pages.InsertOnSubmit(pageData);

                if (isMultiBlog)
                {
                    BlogData.BlogPage blogPage = new BlogEngine.SQLServer.BlogData.BlogPage()
                    {
                        BlogId = Utils.GetBlogId(),
                        PageId = page.Id
                    };
                    context.BlogPages.InsertOnSubmit(blogPage);
                }

                context.SubmitChanges();
            }
        }

        public override void UpdatePage(Page page)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                BlogData.Page pageData = (from p in context.Pages
                                      where p.PageID == page.Id
                                      select p).FirstOrDefault();
                if (pageData != null)
                {
                    pageData.Title = page.Title;
                    pageData.Description = page.Description;
                    pageData.PageContent = page.Content;
                    pageData.Keywords = page.Keywords;
                    pageData.DateCreated = page.DateCreated;
                    pageData.DateModified = page.DateModified;
                    pageData.IsPublished = page.IsPublished;
                    pageData.IsFrontPage = page.IsFrontPage;
                    pageData.Parent = page.Parent;
                    pageData.ShowInList = page.ShowInList;
                }
                context.SubmitChanges();
            }
        }

        public override void DeletePage(Page page)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var pageData = from p in context.Pages
                               where p.PageID == page.Id
                               select p;
                context.Pages.DeleteAllOnSubmit(pageData);

                if (isMultiBlog)
                {
                    var blogPages = pageData.SelectMany(p => p.BlogPages);
                    context.BlogPages.DeleteAllOnSubmit(blogPages);
                }

                context.SubmitChanges();
            }
        }

        public override List<Page> FillPages()
        {
            List<Page> pages = new List<Page>();
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var ids = from page in context.Pages
                          select page.PageID;

                if (isMultiBlog)
                {
                    ids = from page in context.Pages
                          join blogPage in context.BlogPages on page.PageID equals blogPage.PageId
                          where blogPage.BlogId == Utils.GetBlogId()
                          select page.PageID;
                }

                pages.AddRange(ids.Select(id => Page.Load(id)));
            }
            return pages;
        }

        public override AuthorProfile SelectProfile(string id)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var profileSettings = from pro in context.Profiles
                                      where pro.UserName == id
                                      select pro;
                if (isMultiBlog)
                {
                    profileSettings = from pro in profileSettings
                                      join blogPro in context.BlogProfiles on pro.ProfileID equals blogPro.ProfileId
                                      where blogPro.BlogId == Utils.GetBlogId()
                                      select pro;
                }
                foreach (BlogData.Profile profile in profileSettings)
                {

                    dic.Add(profile.SettingName, profile.SettingValue);
                }
            }

            return loadProfileFromDictionary(dic);
        }

        private static AuthorProfile loadProfileFromDictionary(Dictionary<string,string> dic)
        {
            // Stole this pretty much wholesale from DbBlogProvider (only refactored into its own method)

            AuthorProfile profile = new AuthorProfile();
            // Load profile with data from dictionary
            if (dic.ContainsKey("DisplayName"))
                profile.DisplayName = dic["DisplayName"];
            if (dic.ContainsKey("FirstName"))
                profile.FirstName = dic["FirstName"];
            if (dic.ContainsKey("MiddleName"))
                profile.MiddleName = dic["MiddleName"];
            if (dic.ContainsKey("LastName"))
                profile.LastName = dic["LastName"];
            if (dic.ContainsKey("CityTown"))
                profile.LastName = dic["CityTown"];
            if (dic.ContainsKey("RegionState"))
                profile.LastName = dic["RegionState"];
            if (dic.ContainsKey("Country"))
                profile.LastName = dic["Country"];
            if (dic.ContainsKey("Birthday"))
            {
                DateTime date;
                if (DateTime.TryParse(dic["Birthday"], out date))
                    profile.Birthday = date;
            }
            if (dic.ContainsKey("AboutMe"))
                profile.LastName = dic["AboutMe"];
            if (dic.ContainsKey("PhotoURL"))
                profile.LastName = dic["PhotoURL"];
            if (dic.ContainsKey("Company"))
                profile.LastName = dic["Company"];
            if (dic.ContainsKey("EmailAddress"))
                profile.LastName = dic["EmailAddress"];
            if (dic.ContainsKey("PhoneMain"))
                profile.LastName = dic["PhoneMain"];
            if (dic.ContainsKey("PhoneMobile"))
                profile.LastName = dic["PhoneMobile"];
            if (dic.ContainsKey("PhoneFax"))
                profile.LastName = dic["PhoneFax"];
            if (dic.ContainsKey("IsPrivate"))
                profile.IsPrivate = dic["IsPrivate"].Equals("true", StringComparison.InvariantCultureIgnoreCase);

            return profile;
        }

        public override void InsertProfile(AuthorProfile profile)
        {
            UpdateProfile(profile);
        }

        public override void UpdateProfile(AuthorProfile profile)
        {
            DeleteProfile(profile);

            Dictionary<string, string> dic = loadDictionaryFromProfile(profile);
            using (BlogData.BlogDataContext context = getNewContext())
            {
                context.Profiles.InsertAllOnSubmit(dic.Select(d => new BlogData.Profile() { UserName = profile.UserName, SettingName = d.Key, SettingValue = d.Value }));
                context.SubmitChanges();

                if (isMultiBlog)
                {
                    Guid blogId = Utils.GetBlogId();
                    var profiles = from p in context.Profiles
                                   where p.UserName == profile.UserName &&
                                   !(from blogProfile in context.BlogProfiles
                                     select blogProfile.ProfileId).Contains(p.ProfileID)
                                   select p;
                    List<int> profileIds = profiles.Select(p => p.ProfileID).ToList();
                    context.BlogProfiles.InsertAllOnSubmit(profileIds.Select(p => new BlogData.BlogProfile() { ProfileId = p, BlogId = blogId }));
                }
                context.SubmitChanges();
            }
        }

        private static Dictionary<string, string> loadDictionaryFromProfile(AuthorProfile profile)
        {
            // Stole this pretty much wholesale from DbBlogProvider wholesale. Changed to a generic Dictionary, though.

            // Create Profile Dictionary
            Dictionary<string, string> dic = new Dictionary<string, string>();

            if (!String.IsNullOrEmpty(profile.DisplayName))
                dic.Add("DisplayName", profile.DisplayName);
            if (!String.IsNullOrEmpty(profile.FirstName))
                dic.Add("FirstName", profile.FirstName);
            if (!String.IsNullOrEmpty(profile.MiddleName))
                dic.Add("MiddleName", profile.MiddleName);
            if (!String.IsNullOrEmpty(profile.LastName))
                dic.Add("LastName", profile.LastName);
            if (!String.IsNullOrEmpty(profile.CityTown))
                dic.Add("CityTown", profile.CityTown);
            if (!String.IsNullOrEmpty(profile.RegionState))
                dic.Add("RegionState", profile.RegionState);
            if (!String.IsNullOrEmpty(profile.Country))
                dic.Add("Country", profile.Country);
            if (!String.IsNullOrEmpty(profile.AboutMe))
                dic.Add("AboutMe", profile.AboutMe);
            if (!String.IsNullOrEmpty(profile.PhotoURL))
                dic.Add("PhotoURL", profile.PhotoURL);
            if (!String.IsNullOrEmpty(profile.Company))
                dic.Add("Company", profile.Company);
            if (!String.IsNullOrEmpty(profile.EmailAddress))
                dic.Add("EmailAddress", profile.EmailAddress);
            if (!String.IsNullOrEmpty(profile.PhoneMain))
                dic.Add("PhoneMain", profile.PhoneMain);
            if (!String.IsNullOrEmpty(profile.PhoneMobile))
                dic.Add("PhoneMobile", profile.PhoneMobile);
            if (!String.IsNullOrEmpty(profile.PhoneFax))
                dic.Add("PhoneFax", profile.PhoneFax);
            if (profile.Birthday != DateTime.MinValue)
                dic.Add("Birthday", profile.Birthday.ToString("yyyy-MM-dd"));

            dic.Add("IsPrivate", profile.IsPrivate.ToString());
            return dic;
        }

        public override void DeleteProfile(AuthorProfile profile)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var profileSettings = from p in context.Profiles
                                      where p.UserName == profile.UserName
                                      select p;
                if (isMultiBlog)
                {
                    profileSettings = from p in profileSettings
                                      join bp in context.BlogProfiles on p.ProfileID equals bp.ProfileId
                                      where bp.BlogId == Utils.GetBlogId()
                                      select p;

                    var blogProfiles = profileSettings.SelectMany(p => p.BlogProfiles);
                    context.BlogProfiles.DeleteAllOnSubmit(blogProfiles);
                }
                context.Profiles.DeleteAllOnSubmit(profileSettings);
                context.SubmitChanges();
            }
        }

        public override List<AuthorProfile> FillProfiles()
        {
            List<AuthorProfile> profiles = new List<AuthorProfile>();
            IEnumerable<string> profileNames;
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var profileData = from p in context.Profiles
                                  select p;
                if (isMultiBlog)
                {
                    profileData = from p in profileData
                                  join bp in context.BlogProfiles on p.ProfileID equals bp.ProfileId
                                  where bp.BlogId == Utils.GetBlogId()
                                  select p;
                }

                profileNames = (from profile in profileData
                                select profile.UserName).Distinct();
                profiles.AddRange(profileNames.Select(n => BusinessBase<AuthorProfile, string>.Load(n)));
            }
            return profiles;
        }

        public override Category SelectCategory(Guid id)
        {
            Category cat = Category.Categories.Where(c => c.Id == id).FirstOrDefault();
            if (cat == null)
            {
                cat = new Category()
                {
                    Id = id
                };
            }
            cat.MarkOld();
            return cat;
        }

        public override void InsertCategory(Category category)
        {
            Category.Categories.Add(category);
            BlogData.Category categoryData = new BlogData.Category()
            {
                CategoryID = category.Id,
                CategoryName = category.Title,
                Description = category.Description
            };
            if (category.Parent.HasValue)
            {
                categoryData.ParentID = category.Parent.Value;
            }
            using (BlogData.BlogDataContext context = getNewContext())
            {
                context.Categories.InsertOnSubmit(categoryData);
                if (isMultiBlog)
                {
                    context.BlogCategories.InsertOnSubmit(new BlogData.BlogCategory() { CategoryId = category.Id, BlogId = Utils.GetBlogId() });
                }
                context.SubmitChanges();
            }
        }

        public override void UpdateCategory(Category category)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                BlogData.Category categoryData = (from cat in context.Categories
                                              where cat.CategoryID == category.Id
                                              select cat).FirstOrDefault();
                if (categoryData != null)
                {
                    categoryData.CategoryName = category.Title;
                    categoryData.Description = category.Description;
                    categoryData.ParentID = category.Parent;
                }
                context.SubmitChanges();
            }
        }

        public override void DeleteCategory(Category category)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var categories = from cat in context.Categories
                                 where cat.CategoryID == category.Id
                                 select cat;
                context.Categories.DeleteAllOnSubmit(categories);

                if (isMultiBlog)
                {
                    context.BlogCategories.DeleteAllOnSubmit(categories.SelectMany(c => c.BlogCategories));
                }
                context.SubmitChanges();
            }
        }

        public override List<Category> FillCategories()
        {
            List<Category> categories = new List<Category>();
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var categoryData = from cat in context.Categories
                                   select cat;
                if (isMultiBlog)
                {
                    categoryData = from cat in categoryData
                                   join bcat in context.BlogCategories on cat.CategoryID equals bcat.CategoryId
                                   where bcat.BlogId == Utils.GetBlogId()
                                   select cat;
                }
                categories.AddRange(categoryData.Select(c => new Category(c.CategoryName, c.Description) { Parent = c.ParentID, Id = c.CategoryID }));
            }
            categories.ForEach(c =>
            {
                if (c.Description == null)
                {
                    c.Description = ""; // category editing blows up if it finds a null description.
                }
                c.MarkOld();
            });
            return categories;
        }

        public override StringDictionary LoadSettings()
        {
            StringDictionary dic = new StringDictionary();
            using (BlogData.BlogDataContext context = getNewContext())
            {
                if (!isMultiBlog)
                {
                    List<BlogData.Setting> settings = (from setting in context.Settings
                                                       select setting).ToList();
                    settings.ForEach(s => dic.Add(s.SettingName, s.SettingValue));
                }
                else
                {
                    List<BlogData.MultiSetting> settings = (from setting in context.MultiSettings
                                                            where setting.BlogId == Utils.GetBlogId()
                                                            select setting).ToList();
                    settings.ForEach(s => dic.Add(s.SettingName, s.SettingValue));
                }
            }
            return dic;
        }

        public override void SaveSettings(StringDictionary settings)
        {
            Dictionary<string, string> dic = settings.ToDictionary();

            if (!isMultiBlog)
            {
                saveSingleSettings(dic);
            }
            else
            {
                saveMultiSettings(dic);
            }

        }

        private void saveMultiSettings(Dictionary<string, string> dic)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                Guid blogId = Utils.GetBlogId();
                var currentSettings = from setting in context.MultiSettings
                                      where setting.BlogId == blogId
                                      select setting;

                var deleteSettings = from setting in currentSettings
                                     where !dic.Keys.Contains(setting.SettingName)
                                     select setting;

                var insertSettings = dic.Keys.Where(d => !currentSettings.Select(s => s.SettingName).Contains(d));


                List<BlogData.MultiSetting> updateList = currentSettings.Except(deleteSettings).ToList();
                updateList.ForEach(u => u.SettingValue = dic[u.SettingName]);
                context.MultiSettings.DeleteAllOnSubmit(deleteSettings);
                context.MultiSettings.InsertAllOnSubmit(insertSettings.Select(i => new BlogData.MultiSetting() { BlogId = blogId, SettingName = i, SettingValue = dic[i] }));
                context.SubmitChanges();
            }
        }

        private void saveSingleSettings(Dictionary<string, string> dic)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var currentSettings = from setting in context.Settings
                                      select setting;

                var deleteSettings = from setting in currentSettings
                                     where !dic.Keys.Contains(setting.SettingName)
                                     select setting;

                var insertSettings = dic.Keys.Where(d => !currentSettings.Select(s => s.SettingName).Contains(d));


                List<BlogData.Setting> updateList = currentSettings.Except(deleteSettings).ToList();
                updateList.ForEach(u => u.SettingValue = dic[u.SettingName]);
                context.Settings.DeleteAllOnSubmit(deleteSettings);
                context.Settings.InsertAllOnSubmit(insertSettings.Select(i => new BlogData.Setting() { SettingName = i, SettingValue = dic[i] }));
                context.SubmitChanges();
            }
        }

        public override StringCollection LoadPingServices()
        {
            StringCollection pings = new StringCollection();
            using (BlogData.BlogDataContext context = getNewContext())
            {
                List<BlogData.PingService> pingData = (from p in context.PingServices
                                                   select p).ToList();
                pings.AddRange(pingData.Select(p => p.Link).ToArray());
            }
            
            return pings;
        }

        public override void SavePingServices(StringCollection services)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var currentPings = from ping in context.PingServices
                                   select ping;
                context.PingServices.DeleteAllOnSubmit(currentPings);
                context.SubmitChanges();

                services.ToList().ForEach(p => context.PingServices.InsertOnSubmit(new BlogData.PingService() { Link = p }));
                context.SubmitChanges();
            }
        }

        public override StringCollection LoadStopWords()
        {
            StringCollection words = new StringCollection();
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var stopWords = from word in context.StopWords
                                select word;
                words.AddRange(stopWords.Select(w => w.Word).ToArray());
            }
            return words;
        }

        public override object LoadFromDataStore(ExtensionType exType, string exId)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                return (from ds in context.DataStoreSettings
                        where ds.ExtensionType == exType.GetHashCode().ToString() && ds.ExtensionId == exId
                        select ds.Settings).FirstOrDefault();
            }
        }

        public override void SaveToDataStore(ExtensionType exType, string exId, object settings)
        {
            XmlSerializer xs = new XmlSerializer(settings.GetType());
            string objectXML = string.Empty;
            using (StringWriter sw = new StringWriter())
            {
                xs.Serialize(sw, settings);
                objectXML = sw.ToString();
            }

            using (BlogData.BlogDataContext context = getNewContext())
            {
                BlogData.DataStoreSetting data = (from ds in context.DataStoreSettings
                                                  where ds.ExtensionId == exId && ds.ExtensionType == exType.GetHashCode().ToString()
                                                  select ds).FirstOrDefault();
                if (data == null)
                {
                    context.DataStoreSettings.InsertOnSubmit(new BlogData.DataStoreSetting() { ExtensionId = exId, ExtensionType = exType.GetHashCode().ToString(), Settings = objectXML });
                }
                else
                {
                    data.Settings = objectXML;
                }
                context.SubmitChanges();
            }
        }

        public override void RemoveFromDataStore(ExtensionType exType, string exId)
        {
            using (BlogData.BlogDataContext context = getNewContext())
            {
                var data = from ds in context.DataStoreSettings
                           where ds.ExtensionId == exId && ds.ExtensionType == exType.GetHashCode().ToString()
                           select ds;
                context.DataStoreSettings.DeleteAllOnSubmit(data);
                context.SubmitChanges();
            }
        }

        public override string StorageLocation()
        {
            if (String.IsNullOrEmpty(System.Web.Configuration.WebConfigurationManager.AppSettings["StorageLocation"]))
                return @"~/app_data/";
            return System.Web.Configuration.WebConfigurationManager.AppSettings["StorageLocation"];
        }

        private BlogData.BlogDataContext getNewContext()
        {
            return Utils.getNewBlogContext(connString, tablePrefix, schema);
        }
    }
}
